Attribute VB_Name = "Module1"
Dim c, c1, c2 As Connection
Dim rs As Recordset
Dim rs1, rs2, rs3, rs4 As Recordset
Dim b As Boolean
Public Sub senior(ByVal st As String, ByVal cost As Double, ByVal due As Integer)
Dim s(1000) As String
Dim i, j, l, d As Integer
Call database
rs.Open "select IntroNo, CodeNo from AgentInfo where CodeNo='" & st & "'", c
If rs.Fields(0) <> rs.Fields(1) Then
Do
If j = 0 Then
While Not rs.EOF
s(l) = rs.Fields(0)
l = l + 1
j = l
rs.MoveNext
Wend
End If
Call database1
rs1.Open "select IntroNo from AgentInfo where CodeNo='" & s(i) & "' and CodeNo <> IntroNo", c
While Not rs1.EOF
s(j) = rs1.Fields(0)
j = j + 1
rs1.MoveNext
Wend
i = i + 1
Loop While i < j
End If
Call database
rs.Open "select * from AgentInfo where CodeNo='" & st & "'", c
rs.Fields(4) = Val(Switch(IsNull(rs.Fields(4)), 0, rs.Fields(4) = 0, 0, rs.Fields(4), rs.Fields(4))) + cost
'MsgBox Format(Now, "mmm")
If due <= 12 Then
d = 1
ElseIf due > 12 And due <= 24 Then
d = 2
ElseIf due = 100 Then
d = 4
Else
d = 3
End If
rs2.Open "Select " & Format(Jeevan.txtdate.Value, "mmm") & d & " from Salary where CodeNo='" & st & "'", c
'rs3.Open "select * from AgentInfo where codeNo='" & st & "'", c
rs2.Fields(0) = Switch(rs2.Fields(0) = "" Or IsNull(rs2.Fields(0)), 0, rs2.Fields(0) = 0, 0, rs2.Fields(0), rs2.Fields(0)) + Switch(d = 4, (cost * 100) / 20, 1, cost)
rs.Update
rs2.Update
For i = 0 To j - 1
If s(i) <> rs.Fields(1) Then
Call database
rs.Open "select * from AgentInfo where CodeNo='" & s(i) & "'", c
rs.Fields(4) = Val(Switch(IsNull(rs.Fields(4)) Or rs.Fields(4) = "" Or rs.Fields(4) = "0", 0, rs.Fields(4), rs.Fields(4))) + cost
rs2.Open "Select " & Format(Jeevan.txtdate.Value, "mmm") & d & " from Salary where CodeNo='" & s(i) & "'", c
'rs3.Open "select * from AgentInfo where codeNo='" & s(i) & "'", c
rs2.Fields(0) = Switch(IsNull(rs2.Fields(0)) Or rs2.Fields(0) = "" Or rs2.Fields(0) = 0, 0, rs2.Fields(0), rs2.Fields(0)) + Switch(d = 4, (cost * 100) / 20, 1, cost)
rs.Update
rs2.Update
'MsgBox "record from agent is update"
End If
Next i
End Sub
Private Sub database()
Set c = New Connection
c.Provider = "Microsoft.Jet.OLEDB.4.0"
c.Open App.Path & "\Jeevan.mdb"
Set rs = New Recordset
rs.LockType = adLockOptimistic
rs.CursorLocation = adUseServer
rs.CursorType = adOpenKeyset
Set rs2 = New Recordset
rs2.LockType = adLockOptimistic
rs2.CursorLocation = adUseServer
rs2.CursorType = adOpenKeyset
Set rs3 = New Recordset
rs3.LockType = adLockOptimistic
rs3.CursorLocation = adUseServer
rs3.CursorType = adOpenKeyset
End Sub
Private Sub database1()
Set c = New Connection
c.Provider = "Microsoft.Jet.OLEDB.4.0"
c.Open App.Path & "\Jeevan.mdb"
Set rs1 = New Recordset
rs1.LockType = adLockOptimistic
rs1.CursorLocation = adUseServer
rs1.CursorType = adOpenKeyset
End Sub

Public Function printword(n As Long, ch As String) As String
Dim one() As String
Dim ten() As String
Dim s As String
ReDim one(20)
one(0) = ""
one(1) = "One"
one(2) = "Two"
one(3) = "Three"
one(4) = "Four"
one(5) = "Five"
one(6) = "Six"
one(7) = "Seven"
one(8) = "Eight"
one(9) = "Nine"
one(10) = "Ten"
one(11) = "Eleven"
one(12) = "Twelve"
one(13) = "Thirteen"
one(14) = "Fourteen"
one(15) = "Fifteen"
one(16) = "Sixteen"
one(17) = "Seventeen"
one(18) = "Eighteen"
one(19) = "Nineteen"
ReDim ten(10)
ten(0) = ""
ten(1) = "Twenty"
ten(2) = "Thirty"
ten(3) = "Fourty"
ten(4) = "Fifty"
ten(5) = "Sixty"
ten(6) = "Seventy"
ten(7) = "Eighty"
ten(8) = "Ninety"
If n <> 0 Then
If n > 19 Then
s = ten(Int(n / 10) - 1) + " " + one(n Mod 10) + ch
Else
s = one(n) + ch
End If
End If
printword = s + " "
End Function
Public Function convert(ByVal X As Long) As String
Dim s As String
s = " "
s = printword(Int(X / 10000000), " Crore") + printword(Int(Int(X / 100000) Mod 100), " Lakh") + printword(Int(Int((X / 1000)) Mod 100), " Thousand") + printword(Int(Int(X Mod 1000) / 100), " Hundred") + printword(Int(X Mod 100), " ")
convert = s
End Function

Public Function emp(a As String) As Boolean
Call database1
rs1.Open "Select CodeNo from AgentInfo where CodeNo='" & a & "'", c
If rs1.RecordCount = 1 Then
emp = False
Else
emp = True
End If
rs1.Close
End Function

Public Function datechange(d As String) As String
Dim s, c As String
s = Trim(d)
If Mid(s, 2, 1) = "/" And Mid(s, 4, 1) = "/" Then
s = Mid(s, 1, 8)
s = "0" & Mid(s, 3, 1) & "/" & "0" & Mid(s, 1, 1) & "/" & Mid(s, 5, 4)
ElseIf Mid(s, 2, 1) = "/" Then
s = Mid(s, 1, 9)
s = Mid(s, 3, 2) & "/" & "0" & Mid(s, 1, 1) & "/" & Mid(s, 6, 4)
ElseIf Mid(s, 5, 1) = "/" Then
s = Mid(s, 1, 9)
s = "0" & Mid(s, 4, 1) & "/" & Mid(s, 1, 2) & "/" & Mid(s, 6, 4)
Else
s = Mid(s, 1, 10)
s = Mid(s, 4, 2) & "/" & Mid(s, 1, 2) & "/" & Mid(s, 7, 4)
End If
datechange = s
End Function

Public Function days(ByVal f As String) As String
Dim s As String
s = f
days = Mid(s, 1, 2)
End Function

Public Function months(ByVal f As String) As String
Dim s As String
s = f
months = Mid(s, 4, 2)
End Function

Public Sub copydatabase(s As String)
Set c2 = New Connection
c2.Provider = "Microsoft.Jet.OLEDB.4.0"
If rs4!PlanNo = "PlanA" Or rs4!PlanNo = "PlanC" Then
c2.Open App.Path & "\" & rs4!PlanNo & ".mdb"
Set rs3 = New Recordset
rs3.CursorLocation = adUseServer
rs3.CursorType = adOpenKeyset
rs3.LockType = adLockOptimistic
rs3.Open "select * from " & rs4!PlanNo & rs4!mop & " where JVM='" & s & "'", c2
'Else
'c.Open App.Path & "\" & rs4!PlanNo & rs4!mop & ".mdb"
'Set rs3 = New Recordset
'rs3.CursorLocation = adUseServer
'rs3.CursorType = adOpenKeyset
'rs3.LockType = adLockOptimistic
'rs3.Open "select * from " & rs4!PlanNo & rs4!Term & " where JVM='" & s & "'", c
End If
End Sub
Public Sub copydatabase1(s As String)
Set c1 = New Connection
c1.Provider = "Microsoft.Jet.OLEDB.4.0"
c1.Open App.Path & "\jeevan.mdb"
Set rs4 = New Recordset
rs4.CursorLocation = adUseServer
rs4.CursorType = adOpenKeyset
rs4.LockType = adLockOptimistic
rs4.Open "select * from customer where JVM='" & s & "' and (PlanNo='PlanA' or PlanNo='PlanC')", c1
If rs4.RecordCount = 0 Then
b = False
Else
b = True
End If
End Sub
Public Function copycurrentdue(s As String) As Integer
If b Then
Call copydatabase(s)
For i = 2 To rs3.Fields().Count - 1
If IsNull(rs3.Fields(i)) Then
copycurrentdue = (i / 2) - 1
Exit For
End If
Next i
End If
End Function
Public Function copycurrentAcc(s As String) As Integer
Dim l As Integer
If b Then
Call copydatabase(s)
For i = 2 To rs3.Fields().Count - 1 Step 2
If Not IsNull(rs3.Fields(i)) And rs3.Fields(i) <> "" Then l = l + rs3.Fields(i)
If IsNull(rs3.Fields(i)) Or rs3.Fields(i) = "" Then
copycurrentAcc = l
Exit For
End If
Next i
End If
End Function

Public Function copylastpay(s As String) As Date
If b Then
Call copydatabase(s)
For i = 2 To rs3.Fields().Count - 1
If IsNull(rs3.Fields(i)) Or rs3.Fields(i) = "" Then
If i - 1 <> 1 Then copylastpay = rs3.Fields(i - 1)
Exit For
End If
Next i
End If
End Function

